
import pandas as pd
import matplotlib.pyplot as plt
import lasio
import warnings
import missingno as msno
import seaborn as sns
warnings.filterwarnings('ignore')


# df= pd.read_csv("Hibernia B-16 38 - FDP 311 mm Section LAS-011361.csv")   

# To run this cell, remove '#' from above line


# Read the las file

las311 = lasio.read("Hibernia B-16 38 - FDP 311 mm Section LAS-011361.las")


# Check for meta data
# We will get details of the drilling activities such as time, date, locations, company name etc.

for item in las311.sections['Well']:
    print(f"{item.descr} ({item.mnemonic}): \t\t  {item.value}")

START TIME(OLE Automation date) (STRT): 		  41,586.4802777778
STOP TIME(OLE Automation date) (STOP): 		  41,597.5413888889
STEP TIME(60s in time) (STEP): 		  0
NULL VALUE (NULL): 		  -999.25
COMPANY (COMP): 		  HMDC          ExxonMobil Canada Use Only
WELL NAME (WELL): 		  B-16 38 (OPNN1)
FIELD NAME (FLD): 		  Hibernia
Rig Name (RIGN): 		  M-71 East Rig
Rig Type (RIGTYP): 		  Platform
Service Order Number (SON): 		  13CNF0009
SERVICE COMPANY (SRVC): 		  Schlumberger
LOG DATE(Composite Date for Composite LAP) (MM/DD/YYYY) (DATE): 		  11/08/2013
County Label (CLAB): 		  County:
COUNTY (CNTY): 		  
State/Province Label (SLAB): 		  Province:
STATE or PROVINCE (STAT): 		  Newfoundland and Labrador
STATE or PROVINCE (PROV): 		  Newfoundland and Labrador
COUNTRY (CTRY): 		  Canada
Continent Region (CONT_REGION): 		  N. America
Section (SECT): 		  
Township (TOWN): 		  
Range (RANG): 		  
API NUMBER (API): 		  
UNIQUE WELL ID (UWI): 		  821939
Logging Unit Location (LUL): 		  Pipe Deck
Logging Unit Number (LUN): 		  OLU-HD-9604
FIELD LOCATION (LOC): 		  Grand Banks
Field Location Line 1 (FL1): 		  X:669440.500 m
Field Location Line 2 (FL2): 		  Y:5179815.290 m
LATITUDE (LATI): 		  46° 45' 1.37" N
LONGITUDE (LONG): 		  48° 46' 53.571" W
Local Permanent Datum (PDAT): 		  MSL
Logging Measured From (LMF): 		  DF
Elevation of Depth Reference (LMF) Above Permanent Datum (APD): 		  76.0
Elevation of Permanent Datum (PDAT) above Mean Sea Level (EPD): 		  0.0
Elevation of Kelly Bushing Above Permanent Datum (EKB): 		  -999.25
Elevation of Drill Floor Above Permanent Datum (EDF): 		  76.0
Elevation of Ground Level Above Permanent Datum (EGL): 		  -80.0
Elevation of Casing Flange above Permanent Datum (ECF): 		  -999.25


# Check for data channels 

for curve in las311.curves:
    print(curve.mnemonic)

TIME_1900
TIME
DEPTH
RPM
STOR
DTOR
SWOB
DWOB
ROP5
SMSE
TFLO
ECD
SPPA
VIB_LAT
VIB_X
VIB_TOR
STICKRATIO
STICK
HKLA
COBTM
BLKP
PVEL
CRPM
TRPM
SHKRSK
SHOK_RSK
AZIM_CONT
INCL_CONT
DHAP


# Check for Units and descriptions
  
for curve in las311.curves:
    print(f"{curve.mnemonic}:\t{curve.unit}:\t{curve.descr}")

TIME_1900:	d:	Time Index(OLE Automation date)
TIME:	s:	(60s)                   Time(hh mm ss/dd-MMM-yyyy)
DEPTH:	m:	(RT)    (DnMWorkflow)                                   Depth Index
RPM:	c/min:	(RT)    (DRILLING_SURFACE)                              Rotational Speed
STOR:	kN.m:	(RT)    (DRILLING_SURFACE)                              Surface Torque
DTOR:	kN.m:	(RT)    (TELE900-IWOB)                                  Downhole Torque (MWD)
SWOB:	1000 kgf:	(RT)    (DRILLING_SURFACE)                              Surface Weight On Bit
DWOB:	1000 kgf:	(RT)    (TELE900-IWOB)                                  Downhole Weight on Bit
ROP5:	m/h:	(RT)    (DRILLING_SURFACE)                              Rate of penetration averaged over the last 5 ft (1.5 m)
SMSE:	1000 psi:	(RT)    (Borehole)                                      Surface Mechanical Specific Energy
TFLO:	L/min:	(RT)    (DRILLING_SURFACE)                              Total flow rate of all active pumps
ECD:	kg/m3:	(RM)    (ARC9)                                          Equivalent Circulating Density
SPPA:	kPa:	(RT)    (DRILLING_SURFACE)                              Standpipe Pressure
VIB_LAT:	gn:	(RT)    (TELE900-IWOB)                                  Transverse RMS Vibration
VIB_X:	gn:	(RT)    (TELE900-IWOB)                                  RMS Vibration, X-Axis
VIB_TOR:	N.m:	(RT)    (TELE900-IWOB)                                  Torsional RMS Vibration
STICKRATIO:	:	(RT)    (TELE900-IWOB)                                  Stick Ratio
STICK:	c/min:	(RT)    (TELE900-IWOB)                                  Stick Slip Indicator
HKLA:	1000 kgf:	(RT)    (DRILLING_SURFACE)                              Average Hookload
COBTM:	:	(RT)    (DRILLING_SURFACE)                              Composite On Bottom Status
BLKP:	m:	(RT)    (DRILLING_SURFACE)                              Height of block above rig floor
PVEL:	m/s:	(RT)    (DRILLING_SURFACE)                              Pipe Velocity
CRPM:	c/min:	(RT)    (TELE900-IWOB)                                  Collar Rotational Speed
TRPM:	c/min:	(RT)    (TELE900-IWOB)                                  MWD Turbine Rotation Speed
SHKRSK:	:	(RT)    (TELE900-IWOB)                                  Shock Risk
SHOK_RSK:	:	(RT)    (TST825)                                        Shock Risk
AZIM_CONT:	deg:	(RT)    (TELE900-IWOB)                                  Continuous Hole Azimuth
INCL_CONT:	deg:	(RT)    (TELE900-IWOB)                                  Continuous Inclination (Hole Deviation)
DHAP:	kPa:	(RM)    (ARC9)                                          Downhole Annulus Pressure


# Create a dataframe from LAS file

well311 = las311.df()


# First look at the dataframe
well311


# from pandas_profiling import ProfileReport

# report = ProfileReport(well311)

# report


# report.to_file("Hibernia 1638 Drilling Data Analysis Prifle Report.html")


# Get coloum information and data types
# We can see that first row of data shows TIME but it saved as 'object'

well311.info()

<class 'pandas.core.frame.DataFrame'>
Float64Index: 15929 entries, 41586.4802777778 to 41597.5413888889
Data columns (total 28 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   TIME        15929 non-null  object 
 1   DEPTH       15044 non-null  float64
 2   RPM         15039 non-null  float64
 3   STOR        15039 non-null  float64
 4   DTOR        2762 non-null   float64
 5   SWOB        15044 non-null  float64
 6   DWOB        2754 non-null   float64
 7   ROP5        5779 non-null   float64
 8   SMSE        11883 non-null  float64
 9   TFLO        15039 non-null  float64
 10  ECD         14926 non-null  float64
 11  SPPA        15039 non-null  float64
 12  VIB_LAT     2571 non-null   float64
 13  VIB_X       2599 non-null   float64
 14  VIB_TOR     2468 non-null   float64
 15  STICKRATIO  2422 non-null   float64
 16  STICK       2468 non-null   float64
 17  HKLA        15044 non-null  float64
 18  COBTM       15044 non-null  float64
 19  BLKP        15044 non-null  float64
 20  PVEL        15044 non-null  float64
 21  CRPM        2533 non-null   float64
 22  TRPM        2785 non-null   float64
 23  SHKRSK      2817 non-null   float64
 24  SHOK_RSK    2489 non-null   float64
 25  AZIM_CONT   2376 non-null   float64
 26  INCL_CONT   2214 non-null   float64
 27  DHAP        15811 non-null  float64
dtypes: float64(27), object(1)
memory usage: 3.5+ MB


well311['TIME'] = pd.to_datetime(well311['TIME'])


# Check info again , now we can see that TIME data are as datetime64 format
# Also it shows total number of Non-Null data points for each data types

well311.info()

<class 'pandas.core.frame.DataFrame'>
Float64Index: 15929 entries, 41586.4802777778 to 41597.5413888889
Data columns (total 28 columns):
 #   Column      Non-Null Count  Dtype         
---  ------      --------------  -----         
 0   TIME        15929 non-null  datetime64[ns]
 1   DEPTH       15044 non-null  float64       
 2   RPM         15039 non-null  float64       
 3   STOR        15039 non-null  float64       
 4   DTOR        2762 non-null   float64       
 5   SWOB        15044 non-null  float64       
 6   DWOB        2754 non-null   float64       
 7   ROP5        5779 non-null   float64       
 8   SMSE        11883 non-null  float64       
 9   TFLO        15039 non-null  float64       
 10  ECD         14926 non-null  float64       
 11  SPPA        15039 non-null  float64       
 12  VIB_LAT     2571 non-null   float64       
 13  VIB_X       2599 non-null   float64       
 14  VIB_TOR     2468 non-null   float64       
 15  STICKRATIO  2422 non-null   float64       
 16  STICK       2468 non-null   float64       
 17  HKLA        15044 non-null  float64       
 18  COBTM       15044 non-null  float64       
 19  BLKP        15044 non-null  float64       
 20  PVEL        15044 non-null  float64       
 21  CRPM        2533 non-null   float64       
 22  TRPM        2785 non-null   float64       
 23  SHKRSK      2817 non-null   float64       
 24  SHOK_RSK    2489 non-null   float64       
 25  AZIM_CONT   2376 non-null   float64       
 26  INCL_CONT   2214 non-null   float64       
 27  DHAP        15811 non-null  float64       
dtypes: datetime64[ns](1), float64(27)
memory usage: 3.5 MB


msno.matrix(well311)

<AxesSubplot:>


well311=well311[['TIME', 'DEPTH','SWOB', 'STOR', 'RPM', 'ROP5', 'SPPA', 'SMSE', 'TFLO', 'COBTM', 'ECD']]


# Let's have a look at the dataframe now , it seems that 'TIME_1900' still within the dataframe as index coloum

well311


# Now we will reset the index to drop the  'TIME_1900' coloumn

well311 = well311.reset_index(drop = True)


well311


# Check total number of NaN values()
well311.isnull().sum()

TIME         0
DEPTH      885
SWOB       885
STOR       890
RPM        890
ROP5     10150
SPPA       890
SMSE      4046
TFLO       890
COBTM      885
ECD       1003
dtype: int64


# Now at this point, we want to see the data distribution in a simple subplot

# This will help us to get a quick overview of all data profile and possible outliers in data with qucik visual. For example, if you see ECD profile, it shows a very high spike in data at around 600 meter DEPTH

well311.plot(subplots=True, figsize=(15, 20))
plt.show()


plt.figure(figsize=(12,6))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 10}

plt.plot(well311.TIME, well311.DEPTH)
plt.xlabel('Time')
plt.ylabel('DEPTH')

plt.show()


plt.figure(figsize=(12,6))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 12}

plt.plot(well311.TIME, well311.DEPTH, lw= 0.8, color = 'blue')
plt.xlabel('Time')
plt.ylabel('DEPTH')
plt.gca().invert_yaxis()


df = well311[(well311['ROP5']>0) & (well311['SWOB']>0) & (well311['RPM']>0)& (well311['COBTM']==0)]

df


# Check the index row, we get scattered rows. Therefore we will reset index

df.reset_index(drop=True, inplace = True)


# df dataset only contain drilling data. Now we will plot depth vs time plot again to see actial drilling time and depth

plt.figure(figsize=(12,6))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 12}

plt.plot(df.TIME, df.DEPTH, lw= 0.8, color = 'blue')
plt.xlabel('Time')
plt.ylabel('DEPTH')
plt.gca().invert_yaxis()


well311.COBTM.nunique()

2


well311.COBTM.unique()

array([ 1.,  0., nan])


# Drilling Data
df_COBTM_0= well311[well311['COBTM']==0]


df_COBTM_0.describe()


# Non- Drilling Dataa
df_COBTM_1= well311[well311['COBTM']==1]


df_COBTM_1.describe()


# Geological Marker for Hibernia 1638

#create a function
def formation(DEPTH):
  
    
    if  DEPTH <= 1471:
        return 'Banquereau Formation'
              
    if 1471< DEPTH <= 3195:
        return 'Dawson Canyon Formation'
    
    if 3195 < DEPTH <= 3911:
        return 'Nautilus Formation'
        
    if 3911< DEPTH <= 5168:
        return 'Ben Nevis Formation'
    
    if 5168 < DEPTH <= 5487:
        return 'Avalon Formation'
    
    if 5487 < DEPTH <= 6401:
        return 'Whiterose Formation'
    
    if 6401 < DEPTH <= 6948:
        return 'Hibernia Formation'
        
    if 6948< DEPTH :
        return 'Fortune Bay Formation'
        
    else:
        return 'No Formation Label Found'

# create a new column based on condition
df['Formation'] = df['DEPTH'].apply(formation)


# Check the dataframe with Formation Tag

df


# Check data types, Formation tag stored as 'object' 
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5613 entries, 0 to 5612
Data columns (total 12 columns):
 #   Column     Non-Null Count  Dtype         
---  ------     --------------  -----         
 0   TIME       5613 non-null   datetime64[ns]
 1   DEPTH      5613 non-null   float64       
 2   SWOB       5613 non-null   float64       
 3   STOR       5613 non-null   float64       
 4   RPM        5613 non-null   float64       
 5   ROP5       5613 non-null   float64       
 6   SPPA       5613 non-null   float64       
 7   SMSE       5613 non-null   float64       
 8   TFLO       5613 non-null   float64       
 9   COBTM      5613 non-null   float64       
 10  ECD        5613 non-null   float64       
 11  Formation  5613 non-null   object        
dtypes: datetime64[ns](1), float64(10), object(1)
memory usage: 526.3+ KB


df.describe()


# If we want to see description with catagorial data

df.describe(include='all')


# Let us see the distribution of data point for ROP5. We can set bins size. In this figure, we set bin size = 30

sns.histplot(df.ROP5, bins =30)

<AxesSubplot:xlabel='ROP5', ylabel='Count'>


# We can see that most of the data points are distributed around SWOB = 20 and STOR = 40 as shown by high density in color

KDE = sns.kdeplot(df.SWOB, df.STOR, color = 'blue', shade = True)


ax = sns.boxplot(y=df.ROP5)


ax = sns.violinplot(y=df.ROP5)


ax = sns.violinplot(y=df.SMSE)


plt.figure(figsize=(18,6))

import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 12}

matplotlib.rc('font', **font)



plt.xticks(fontsize=12)
plt.yticks(fontsize=12)


ax = sns.violinplot( y=df.SWOB, x=df.Formation)
ax.set_title("Surface Weight On Bit (SWOB) by Formation")
# ax.set_xlabel("SWOB")
# ax.set_ylabel("Formations")
# ax.tight_layout()


# ax.set_xticklabels(rotation = 45)

plt.show()


# This plot shows 'STOR' vs 'SWOB' distribution for all data points. 
# The color map shows the range of ROP5 within these two data set

plt.figure(figsize=(15,6))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 12}

matplotlib.rc('font', **font)

plt.xticks(fontsize=12)
plt.yticks(fontsize=12)


# Set up the scatter plot
plt.scatter(x='SWOB', y='STOR', data=df, c='ROP5', vmin=1, vmax=70,cmap='rainbow',alpha=0.8,edgecolors='black')

plt.colorbar(label='ROP5')


plt.ylabel('STOR', fontsize=12)
plt.xlabel('SWOB', fontsize=12)

plt.show()


# This plot shows 'STOR' vs 'SWOB' distribution for all data points. 

# The color map shows the range of RPM within these two data set


plt.figure(figsize=(15,6))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 12}

matplotlib.rc('font', **font)

# plt.style.use('bmh')

plt.xticks(fontsize=12)
plt.yticks(fontsize=12)


# Set up the scatter plot
plt.scatter(x='SWOB', y='STOR', data=df, c='RPM', vmin=0, vmax=200,cmap='rainbow',alpha=0.8,edgecolors='black')

plt.colorbar(label='RPM')


# Add in labels for the axes
plt.ylabel('STOR', fontsize=12)
plt.xlabel('SWOB', fontsize=12)

plt.show()


# Data points with different color shows the data from different formations
#

plt.figure(figsize=(10,8))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 10}


ax = sns.scatterplot(x='SWOB', y='STOR', data=df, edgecolors='black', hue = 'Formation',palette="deep")

sns.move_legend(ax, "upper left", bbox_to_anchor=(1, 1))



plt.show()


# This plot shows 'STOR' vs 'SWOB' interactive plot with diffeent formation

import plotly.express as px
fig = px.scatter(df, x='SWOB', y='STOR',  color="Formation",width=800, height=600)

fig.update_traces(marker=dict(size=10,
                              line=dict(width=0.5,
                                        color='DarkSlateGrey')),
                  selector=dict(mode='markers'))


fig.update_layout(legend=dict(
    orientation="h",
    yanchor="bottom",
    y=-0.35,
    xanchor="right",
    x=1
))


fig.show()


# You can save this interactive plot as an html file

fig.write_html("Plotly Interactive.html")


# This plot shows 'SMSE' vs 'ROP5' interactive plot with diffeent formation


import plotly.express as px
fig = px.scatter(df, x='ROP5', y='SMSE',  color="Formation",width=800, height=600)

fig.update_traces(marker=dict(size=10,
                              line=dict(width=0.5,
                                        color='DarkSlateGrey')),
                  selector=dict(mode='markers'))


fig.update_layout(legend=dict(
    orientation="h",
    yanchor="bottom",
    y=-0.35,
    xanchor="right",
    x=1
))


fig.show()


# Consider data point with SMSE less than 600 and plot again
df=df[df.SMSE<600]

import plotly.express as px
fig = px.scatter(df, x='ROP5', y='SMSE',  color="Formation",width=800, height=600)

fig.update_traces(marker=dict(size=10,
                              line=dict(width=0.5,
                                        color='DarkSlateGrey')),
                  selector=dict(mode='markers'))


fig.update_layout(legend=dict(
    orientation="h",
    yanchor="bottom",
    y=-0.35,
    xanchor="right",
    x=1
))


fig.show()


df2 = df[[ 'DEPTH', 'SWOB', 'STOR', 'ROP5', 'RPM', 'SMSE', 'SPPA']]


plt.figure(figsize=(10,8))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 12}

matplotlib.rc('font', **font)


sns.heatmap(df2.corr(),annot=True,linewidth=.5, fmt=".1f",cmap="viridis")

plt.show()


plt.figure(figsize=(7,12))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 12}

matplotlib.rc('font', **font)

# plt.style.use('bmh')

plt.xticks(fontsize=12)
plt.yticks(fontsize=12)


# Set up the scatter plot
plt.plot(df.SWOB, df.DEPTH,label = 'SWOB-raw data')

plt.xlabel('SWOB')
plt.ylabel('DEPTH')

plt.legend()

plt.gca().invert_yaxis()


plt.figure(figsize=(7,12))
import matplotlib
font = {'family' : 'serif',
        'weight' : 'regular',
        'size'   : 12}

matplotlib.rc('font', **font)

# plt.style.use('bmh')

plt.xticks(fontsize=12)
plt.yticks(fontsize=12)


# Set up the scatter plot


plt.plot(df.SWOB, df.DEPTH, color ='blue', label = 'SWOB-raw data')

plt.plot(df.SWOB.rolling(window=50).mean(), df.DEPTH, color ='red', label = 'SWOB-mean data')

plt.legend()

plt.xlabel('SWOB')
plt.ylabel('DEPTH')

plt.gca().invert_yaxis()


# It is common practice in drilling to check different surface parameters behaviour at the same time.

# This figure shows sub-plot with 3 paramters.

fig, (ax1,ax2, ax3) = plt.subplots(1,3, figsize=(12,15),sharey=True)


ax1.plot(df['ROP5'].rolling(window=20).median(),df['DEPTH'], label = 'ROP5', lw =1, color ='green') 

ax2.plot(df['SWOB'].rolling(window=20).median(),df['DEPTH'], label = 'SWOB', lw =1, color ='red')

ax3.plot(df['STOR'].rolling(window=20).median(),df['DEPTH'], label = 'STOR', lw =1, color ='blue')

ax1.set_ylabel('DEPTH')
ax1.set_xlabel('ROP5')
ax2.set_xlabel('SWOB')
ax3.set_xlabel('STOR')

ax1.legend()

ax2.legend()

ax3.legend()

plt.gca().invert_yaxis()


# We can annotante plot area with geological marker, name and seperate geological layer with different color 



fig, (ax1,ax2) = plt.subplots(1,2, figsize=(10,15),sharey=True)


ax1.plot(df['ROP5'].rolling(window=200).mean(),df['DEPTH'], label = 'ROP5', lw =1, color ='green')

ax2.plot(df['SWOB'].rolling(window=200).mean(),df['DEPTH'], label = 'SWOB', lw =1, color ='red')

ax1.set_ylabel('DEPTH')
ax1.set_xlabel('ROP5')
ax2.set_xlabel('SWOB')


#Adding formation tag. Two formation tag added in the figure below, you can try more



plt.axhline(y=3195, color='r', linestyle='-', lw=0.5)
plt.text(5, 3195+150, 'Nautilus Formation', fontsize = 15)


plt.axhline(y=3911, color='r', linestyle='-', lw=0.5)
plt.text(5, 3911+250, 'Ben Nevis Formation', fontsize = 15)



ax2.axhspan(3195,3911,  facecolor='yellow', alpha=0.5)
ax2.axhspan(3911, 5168, facecolor='pink', alpha=0.5)



ax1.legend()

ax2.legend()

plt.gca().invert_yaxis()

plt.show()


# Before saving data to a csv file, let us have a final look on the content.
# There are 5610 rows and 12 columns

df


df.to_csv('Hibernia 1638 drilling data 311 mm section processed.csv', index=False)


load_data = pd.read_csv('Hibernia 1638 drilling data 311 mm section processed.csv')


load_data

	TIME	DEPTH	SWOB	STOR	RPM	ROP5	SPPA	SMSE	TFLO	COBTM	ECD
1184	2013-11-09 07:15:36	2427.5371	14.3337	6.7791	40.0	4.8685	13306.61	1.6884	2775.691	0.0	1500.823
1306	2013-11-09 09:17:36	2429.7649	1.2809	13.2328	99.0	15.8604	12618.78	15.0119	2819.716	0.0	1467.436
1307	2013-11-09 09:18:36	2430.2273	2.6199	14.1276	81.0	22.3100	13522.76	11.9149	3000.393	0.0	1466.613
1308	2013-11-09 09:19:36	2430.7432	2.5669	13.9649	82.0	27.7772	13537.10	9.0085	2978.816	0.0	1467.561
1309	2013-11-09 09:20:36	2431.4077	3.6538	16.5139	81.0	32.4092	13529.93	7.8089	3003.081	0.0	1467.761
...	...	...	...	...	...	...	...	...	...	...	...
11389	2013-11-16 09:20:36	5900.2856	24.0114	34.8581	162.0	6.0121	27855.03	133.8819	3642.172	0.0	1471.615
11391	2013-11-16 09:22:36	5900.4902	23.0168	34.8988	162.0	6.1993	27768.01	290.1676	3599.056	0.0	1472.348
11392	2013-11-16 09:23:36	5900.6245	23.7346	34.1259	163.0	6.1923	27811.24	197.4735	3615.220	0.0	1471.682
11393	2013-11-16 09:24:36	5900.7095	23.6831	32.5261	161.0	6.1032	27831.38	190.6714	3631.421	0.0	1471.443
11398	2013-11-16 09:29:36	5900.9453	20.2213	33.5429	161.0	5.0549	27792.01	419.8892	3620.633	0.0	1471.552

	DEPTH	SWOB	STOR	RPM	ROP5	SPPA	SMSE	TFLO	COBTM	ECD
count	6933.000000	6933.000000	6933.000000	6933.000000	5682.000000	6933.000000	6271.000000	6933.000000	6933.0	6932.000000
mean	4334.837484	10.826162	29.725860	143.246935	37.016594	21570.836029	62.910530	3219.901995	0.0	1490.075345
std	1159.854030	22.062672	13.080287	55.374020	19.074430	8401.916191	73.127006	1122.101221	0.0	32.429993
min	385.135700	-94.217900	0.000000	0.000000	2.403700	-230.491700	-0.600500	0.000000	0.0	1308.659000
25%	3381.523200	10.814400	24.594500	147.000000	23.900300	18868.470000	25.311950	3562.186000	0.0	1476.929500
50%	4598.869600	17.058200	33.326000	162.000000	34.964950	24536.170000	37.558700	3629.604000	0.0	1494.305500
75%	5299.397900	22.603500	38.654400	177.000000	46.647075	27385.770000	66.603100	3670.071000	0.0	1507.009250
max	5900.990700	57.395000	60.103400	189.000000	133.698300	29270.860000	998.064100	3826.446000	0.0	1597.154000

	DEPTH	SWOB	STOR	RPM	ROP5	SPPA	SMSE	TFLO	COBTM	ECD
count	8111.000000	8111.000000	8106.000000	8106.000000	97.000000	8106.000000	5612.000000	8106.000000	8111.0	7994.000000
mean	3598.494426	13.536553	8.749850	38.026277	38.426108	11018.740694	55.646027	1682.932544	1.0	1472.061111
std	1775.213151	42.479407	13.076053	54.283540	20.589197	12124.911380	106.645970	1722.165486	0.0	434.314038
min	23.739300	-102.407700	0.000000	0.000000	2.420600	-228.009600	-0.666600	0.000000	1.0	0.000000
25%	2428.948700	0.000000	0.000000	0.000000	26.119900	58.674400	0.000000	0.000000	1.0	1423.956000
50%	3737.187700	0.633000	0.000000	0.000000	35.115100	2215.458000	0.479700	803.586150	1.0	1452.763500
75%	5194.556400	10.600650	18.571325	95.000000	47.445600	25192.787500	29.061275	3605.340000	1.0	1480.819000
max	5900.983400	177.117000	63.303100	187.000000	109.826900	32469.200000	582.433000	3885.763000	1.0	14181.390000

	TIME	DEPTH	SWOB	STOR	RPM	ROP5	SPPA	SMSE	TFLO	COBTM	ECD	Formation
0	2013-11-09 07:15:36	2427.5371	14.3337	6.7791	40.0	4.8685	13306.61	1.6884	2775.691	0.0	1500.823	Dawson Canyon Formation
1	2013-11-09 09:17:36	2429.7649	1.2809	13.2328	99.0	15.8604	12618.78	15.0119	2819.716	0.0	1467.436	Dawson Canyon Formation
2	2013-11-09 09:18:36	2430.2273	2.6199	14.1276	81.0	22.3100	13522.76	11.9149	3000.393	0.0	1466.613	Dawson Canyon Formation
3	2013-11-09 09:19:36	2430.7432	2.5669	13.9649	82.0	27.7772	13537.10	9.0085	2978.816	0.0	1467.561	Dawson Canyon Formation
4	2013-11-09 09:20:36	2431.4077	3.6538	16.5139	81.0	32.4092	13529.93	7.8089	3003.081	0.0	1467.761	Dawson Canyon Formation
...	...	...	...	...	...	...	...	...	...	...	...	...
5608	2013-11-16 09:20:36	5900.2856	24.0114	34.8581	162.0	6.0121	27855.03	133.8819	3642.172	0.0	1471.615	Whiterose Formation
5609	2013-11-16 09:22:36	5900.4902	23.0168	34.8988	162.0	6.1993	27768.01	290.1676	3599.056	0.0	1472.348	Whiterose Formation
5610	2013-11-16 09:23:36	5900.6245	23.7346	34.1259	163.0	6.1923	27811.24	197.4735	3615.220	0.0	1471.682	Whiterose Formation
5611	2013-11-16 09:24:36	5900.7095	23.6831	32.5261	161.0	6.1032	27831.38	190.6714	3631.421	0.0	1471.443	Whiterose Formation
5612	2013-11-16 09:29:36	5900.9453	20.2213	33.5429	161.0	5.0549	27792.01	419.8892	3620.633	0.0	1471.552	Whiterose Formation

	DEPTH	SWOB	STOR	RPM	ROP5	SPPA	SMSE	TFLO	COBTM	ECD
count	5613.000000	5613.000000	5613.000000	5613.000000	5613.000000	5613.000000	5613.000000	5613.000000	5613.0	5613.000000
mean	4396.805206	18.351829	33.942317	163.461963	37.060107	24106.235461	55.313884	3612.365729	0.0	1499.953169
std	1072.786238	6.765678	8.028504	20.018875	18.973053	3925.575081	58.038318	126.857467	0.0	21.456975
min	2427.537100	0.017100	6.779100	1.000000	3.133200	12618.780000	0.377300	2461.086000	0.0	1441.398000
25%	3451.973400	13.985300	28.878900	155.000000	24.067400	21358.920000	25.632800	3598.148000	0.0	1487.164000
50%	4597.866700	18.322900	35.075000	169.000000	34.988800	25081.610000	36.832500	3639.484000	0.0	1496.926000
75%	5346.030800	23.114200	39.766100	178.000000	46.647300	27499.260000	60.659100	3674.537000	0.0	1515.019000
max	5900.945300	40.733300	59.181500	189.000000	133.698300	29270.860000	981.377000	3826.446000	0.0	1597.154000

Exploratory Data Analysis on Raw Drilling Data¶

Task to do¶

Data Source¶

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb0 in position 3039: invalid start byte¶

Profile Report¶

Profile report generate a comprehensive report for all data.¶

Run next cell and this will generate a report. Explore different features on this report. It will take few minutes to generate the report based on the amount of data¶

Save profile report as html file¶

Data Type¶

Change TIME from object to date time format¶

Let's have a look at the missing data¶

For the rest of the task, we will choose the following data points for our analysis¶

Draw time vs depth profile¶

The above figure contains both drilling and non drilling activities with time. Therefore, we will seperate drilling data from non drilling activity¶

Seperate drilling and not drilling data¶

Plot active drilling data¶

Seperate data based on Composite on Bottom Status¶

Adding Geo Tag to the Surface Data¶

In this steps we will add geological tag to the existing data.¶

Statistical Distribution¶

Statistical Distribution of Data¶

Histogram¶

KDE plot¶

Boxplot¶

Violin Plot¶

Check multiple catagorical data together¶

Scatter Plot¶

Interactive Plot¶

Heatmanp¶

Averaging High Density Data¶

Subplot¶

Adding formation Marker to The Plot¶

Save data to csv file¶

Again load the data file from same location¶

Thank you¶

Email your questions/ comments/suggesstions: mmh710@mun.ca¶

https://github.com/mojammelhuque/Drilling-Data-Analytics ¶

	TIME	DEPTH	RPM	STOR	DTOR	SWOB	DWOB	ROP5	SMSE	TFLO	...	COBTM	BLKP	PVEL	CRPM	TRPM	SHKRSK	SHOK_RSK	AZIM_CONT	INCL_CONT	DHAP
TIME_1900
41586.480278	11:31:36/08-Nov-2013	29.8812	0.0	0.0000	NaN	110.4881	NaN	NaN	NaN	0.0	...	1.0	1.5643	0.0000	NaN	NaN	NaN	NaN	NaN	NaN	NaN
41586.480972	11:32:36/08-Nov-2013	53.1780	0.0	0.0000	NaN	122.7929	NaN	NaN	NaN	0.0	...	1.0	1.3470	0.0117	NaN	NaN	NaN	NaN	NaN	NaN	NaN
41586.481667	11:33:36/08-Nov-2013	53.8603	20.0	0.0678	NaN	123.9415	NaN	NaN	NaN	0.0	...	1.0	0.6671	0.1003	NaN	NaN	NaN	NaN	NaN	NaN	NaN
41586.482361	11:34:36/08-Nov-2013	52.6114	0.0	0.0000	NaN	123.6583	NaN	NaN	NaN	0.0	...	1.0	1.8981	-0.0698	NaN	NaN	NaN	NaN	NaN	NaN	NaN
41586.483056	11:35:36/08-Nov-2013	52.5203	0.0	0.0000	NaN	121.8112	NaN	NaN	NaN	0.0	...	1.0	2.0021	0.0000	NaN	NaN	NaN	NaN	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
41597.538611	12:55:36/19-Nov-2013	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	62.0528
41597.539306	12:56:36/19-Nov-2013	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	48.2633
41597.540000	12:57:36/19-Nov-2013	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	34.4738
41597.540694	12:58:36/19-Nov-2013	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	48.2633
41597.541389	12:59:36/19-Nov-2013	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

	TIME	DEPTH	SWOB	STOR	RPM	ROP5	SPPA	SMSE	TFLO	COBTM	ECD
TIME_1900
41586.480278	2013-11-08 11:31:36	29.8812	110.4881	0.0000	0.0	NaN	-134.0341	NaN	0.0	1.0	NaN
41586.480972	2013-11-08 11:32:36	53.1780	122.7929	0.0000	0.0	NaN	-134.0341	NaN	0.0	1.0	NaN
41586.481667	2013-11-08 11:33:36	53.8603	123.9415	0.0678	20.0	NaN	-135.0683	NaN	0.0	1.0	NaN
41586.482361	2013-11-08 11:34:36	52.6114	123.6583	0.0000	0.0	NaN	-135.4130	NaN	0.0	1.0	NaN
41586.483056	2013-11-08 11:35:36	52.5203	121.8112	0.0000	0.0	NaN	-135.2062	NaN	0.0	1.0	NaN
...	...	...	...	...	...	...	...	...	...	...	...
41597.538611	2013-11-19 12:55:36	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
41597.539306	2013-11-19 12:56:36	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
41597.540000	2013-11-19 12:57:36	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
41597.540694	2013-11-19 12:58:36	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
41597.541389	2013-11-19 12:59:36	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

	TIME	DEPTH	SWOB	STOR	RPM	ROP5	SPPA	SMSE	TFLO	COBTM	ECD	Formation
count	5613	5613.000000	5613.000000	5613.000000	5613.000000	5613.000000	5613.000000	5613.000000	5613.000000	5613.0	5613.000000	5613
unique	5613	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	5
top	2013-11-09 07:15:36	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	Ben Nevis Formation
freq	1	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	1931
first	2013-11-09 07:15:36	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
last	2013-11-16 09:29:36	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
mean	NaN	4396.805206	18.351829	33.942317	163.461963	37.060107	24106.235461	55.313884	3612.365729	0.0	1499.953169	NaN
std	NaN	1072.786238	6.765678	8.028504	20.018875	18.973053	3925.575081	58.038318	126.857467	0.0	21.456975	NaN
min	NaN	2427.537100	0.017100	6.779100	1.000000	3.133200	12618.780000	0.377300	2461.086000	0.0	1441.398000	NaN
25%	NaN	3451.973400	13.985300	28.878900	155.000000	24.067400	21358.920000	25.632800	3598.148000	0.0	1487.164000	NaN
50%	NaN	4597.866700	18.322900	35.075000	169.000000	34.988800	25081.610000	36.832500	3639.484000	0.0	1496.926000	NaN
75%	NaN	5346.030800	23.114200	39.766100	178.000000	46.647300	27499.260000	60.659100	3674.537000	0.0	1515.019000	NaN
max	NaN	5900.945300	40.733300	59.181500	189.000000	133.698300	29270.860000	981.377000	3826.446000	0.0	1597.154000	NaN

Exploratory Data Analysis on Raw Drilling Data¶

Task to do¶

Data Source¶

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb0 in position 3039: invalid start byte¶

Profile Report¶

Profile report generate a comprehensive report for all data.¶

Run next cell and this will generate a report. Explore different features on this report. It will take few minutes to generate the report based on the amount of data¶

Save profile report as html file¶

Data Type¶

Change TIME from object to date time format¶

Let's have a look at the missing data¶

For the rest of the task, we will choose the following data points for our analysis¶

Draw time vs depth profile¶

The above figure contains both drilling and non drilling activities with time. Therefore, we will seperate drilling data from non drilling activity¶

Seperate drilling and not drilling data¶

Plot active drilling data¶

Seperate data based on Composite on Bottom Status¶

Adding Geo Tag to the Surface Data¶

In this steps we will add geological tag to the existing data.¶

Statistical Distribution¶

Statistical Distribution of Data¶

Histogram¶

KDE plot¶

Boxplot¶

Violin Plot¶

Check multiple catagorical data together¶

Scatter Plot¶

Interactive Plot¶

Heatmanp¶

Averaging High Density Data¶

Subplot¶

Adding formation Marker to The Plot¶

Save data to csv file¶

Again load the data file from same location¶

Thank you¶

Email your questions/ comments/suggesstions: mmh710@mun.ca¶

https://github.com/mojammelhuque/Drilling-Data-Analytics¶

https://github.com/mojammelhuque/Drilling-Data-Analytics ¶